/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Control;

import Boundary.*;
import Form.EditDataPresensi;
import Form.EditDataSiswa;
import java.sql.*;
import java.util.*;
import java.util.logging.*;

/**
 *
 * @author Cross Over
 */
public class TUkontrol {

    private Connection conn;

    public TUkontrol(Connection koneksi) {
        this.conn = koneksi;
    }

    public static TUkontrol getKoneksi() throws SQLException {
        TUkontrol kon = new TUkontrol(Tools.DataBaseConnection.KoneksiDataBase());
        return kon;
    }

    public static TUkontrol sqlyog() throws SQLException {
        TUkontrol kon = new TUkontrol(Tools.KoneksiDatabase.KoneksiDataBase());
        return kon;
    }

    public List<TU> getAll() throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rset = null;
        conn.setAutoCommit(false);
        String sql = "select * from rpltsiswa order by nis";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();
        List<TU> tu = new ArrayList<TU>();
        while (rset.next()) {
            siswa s = new siswa();
            TU t = new TU();
            s.setNIS(rset.getString(1));
            t.setNis(s);
            s.setNama(rset.getString(2));
            t.setNama(s);
            s.setKelas(rset.getString(3));
            t.setKelas(s);
            s.setAlamat(rset.getString(4));
            t.setAlamat(s);
            s.setJeniskelamin(rset.getString(5));
            t.setJeniskelamin(s);
            tu.add(t);
        }
        conn.commit();
        return tu;
    }

    public List<TU> getAllpresensi(String tgl, String kelas) throws SQLException {
        PreparedStatement psmt = null;
        ResultSet rset = null;
        String tanggal = tgl;
        String kls = kelas;
        conn.setAutoCommit(false);
        String sql = "SELECT p.nis, s.nama, s.kelas, "
                + "sum(DECODE(p.kodekehadiran,1, kodekehadiran)), "
                + "sum(DECODE(p.kodekehadiran,2, kodekehadiran))/2, "
                + "sum(DECODE(p.kodekehadiran,3, kodekehadiran))/3, "
                + "sum(DECODE(p.kodekehadiran,4, kodekehadiran))/4 "
                + "FROM rpltpresensi p join rpltsiswa s on (p.nis = s.nis)"
                + "where p.tanggal = '" + tanggal + "' AND s.kelas = '" + kls + "' group by p.nis, s.nama, s.kelas ORDER BY p.nis";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();
        List<TU> tu = new ArrayList<TU>();
        while (rset.next()) {
            siswa s = new siswa();
            TU t = new TU();
            s.setNIS(rset.getString(1));
            t.setNis(s);
            s.setNama(rset.getString(2));
            t.setNama(s);
            s.setKelas(rset.getString(3));
            t.setKelas(s);
            s.setMasuk(rset.getString(4));
            t.setMasuk(s);
            s.setIjin(rset.getString(5));
            t.setIjin(s);
            s.setSakit(rset.getString(6));
            t.setSakit(s);
            s.setTidakmasuk(rset.getString(7));
            t.setTidakmasuk(s);
            tu.add(t);
        }
        conn.commit();
        return tu;
    }

    public void Save(TU tu, String Tanggal) throws SQLException {

        String Nis = tu.getNis().getNIS();
        String Nama = tu.getNama().getNama();
        String Password = tu.getPass().getPassword();
        String Jk = tu.getJeniskelamin().getJeniskelamin();
        String alamat = tu.getAlamat().getAlamat();
        String kelas = tu.getKelas().getKelas();
        String noktp = tu.getNktp().getNoKTP();
        String namaortu = tu.getNamaortu().getNamaOrtu();
        String nohp = tu.getNohp().getNoTelp();
        String tgl = Tanggal;
        String query = "insert into rpltsiswa "
                + "values ('" + Nis + "','" + Nama
                + "','" + kelas + "','" + alamat + "','" + Jk + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(query);
        String query2 = "insert into rpltuserlogin "
                + "values ('" + Nis + "','" + Password + "','Siswa')";
        Statement stat2 = conn.createStatement();
        stat2.executeUpdate(query2);
        String query3 = "insert into rpltorangtua "
                + "values ('" + noktp + "','" + namaortu + "','" + nohp + "','" + alamat + "','" + Nis + "')";
        Statement stat3 = conn.createStatement();
        stat3.executeUpdate(query3);
        String query4 = "insert into rpltpresensi "
                + "values ('" + tgl + "','" + Nis + "','4')";
        Statement stat4 = conn.createStatement();
        stat4.executeUpdate(query4);
    }

    public void update(TU tu) throws SQLException {
        String Nis = tu.getNis().getNIS();
        String Nama = tu.getNama().getNama();
        String Jk = tu.getJeniskelamin().getJeniskelamin();
        String alamat = tu.getAlamat().getAlamat();
        String kelas = tu.getKelas().getKelas();
        String sql = "update rpltsiswa set nama = '" + Nama + "',"
                + " jenis_kelamin = '" + Jk + "',"
                + " alamat = '" + alamat + "',"
                + " kelas = '" + kelas + "'"
                + " where nis = '" + Nis + "'";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
    }

    public void delete(TU tu) throws SQLException {

        String nis = tu.getNis().getNIS();
        String sql4 = "delete from rpltorangtua where nis = '" + nis + "'";
        String sql3 = "delete from rpltpresensi where nis = '" + nis + "'";
        String sql2 = "delete from rpltuserlogin where nis = '" + nis + "'";
        String sql = "delete from rpltsiswa where nis = '" + nis + "'";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql4);
        stat.executeUpdate(sql3);
        stat.executeUpdate(sql2);
        stat.executeUpdate(sql);
        
    }

    public List<TU> getSms(String tgl) throws SQLException {
        String tgal = tgl;
        PreparedStatement psmt = null;
        ResultSet rset = null;
        conn.setAutoCommit(false);
        String sql = "select s.nis, s.nama, p.kodekehadiran, o.nama, o.nohp "
                + " from rpltsiswa s, rpltpresensi p, rpltorangtua o "
                + " where s.nis = o.nis and s.nis = p.nis "
                + " and p.tanggal = '" + tgal + "'"
                + " order by s.nis";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();
        List<TU> tu = new ArrayList<TU>();
        while (rset.next()) {
            siswa s = new siswa();
            Presensi p = new Presensi();
            OrangTua o = new OrangTua();
            TU t = new TU();
            s.setNIS(rset.getString(1));
            t.setNis(s);
            s.setNama(rset.getString(2));
            t.setNama(s);
            p.setKodekehadiran(rset.getInt(3));
            t.setStatus(p);
            o.setNamaOrtu(rset.getString(4));
            t.setNamaortu(o);
            o.setNoTelp(rset.getString(5));
            t.setNohp(o);
            tu.add(t);
        }
        conn.commit();
        return tu;
    }

    public void kirimsms(TU tu) throws SQLException {
        String Nama = tu.getNama().getNama();
        int status = tu.getStatus().getKodekehadiran();
        String nohp = tu.getNohp().getNoTelp();
        String tgl = tu.getTaggal().getTanggal();
        String sts = null;
        if (status == 1) {
            sts = "Masuk sekolah";
        } else if (status == 2) {
            sts = "Ijin";
        } else if (status == 3) {
            sts = "Sakit";
        } else {
            sts = "Tidak Hadir ke sekolah";
        }
        String query = "INSERT INTO outbox (Class,DestinationNumber,TextDecoded,SendingDateTime,RelativeValidity,SenderID,DeliveryReport,CreatorId) "
                + "VALUES('-1'," + nohp + ",'Pada tanggal " + tgl + ", Anak anda yang bernama " + Nama + " " + sts + "',NOW(),-1,'','no',1)";
        Statement stat = conn.createStatement();
        stat.executeUpdate(query);
    }

    public String CariSiswa(String nissiswa) throws SQLException {
        CallableStatement callst = null;
        String namanya = "";

        try {
            String sql = "{CALL spCariSiswa(?,?)}";
            callst = conn.prepareCall(sql);
            callst.setString(1, nissiswa);
            callst.registerOutParameter(2, java.sql.Types.VARCHAR);
            callst.execute();
            namanya = callst.getString(2);
            callst.execute();
            System.out.println("nama");
            conn.commit();
            return namanya;
        } catch (SQLException e) {
            Logger.getLogger(EditDataPresensi.class.getName()).log(Level.SEVERE, null, e);
            conn.rollback();
            throw e;
        }
    }

    public TU CariDataSiswa(String nis) throws SQLException {
        TU t = new TU();
        siswa s = new siswa();
        PreparedStatement psmt = null;
        ResultSet rset = null;
        String sql = "select nama, kelas, alamat, jenis_kelamin "
                + " from rpltsiswa "
                + " where nis = '" + nis + "'";
        psmt = conn.prepareCall(sql);
        rset = psmt.executeQuery();
        while (rset.next()) {
            s.setNama(rset.getString(1));
            t.setNama(s);
            s.setKelas(rset.getString(2));
            t.setKelas(s);
            s.setAlamat(rset.getString(3));
            t.setAlamat(s);
            s.setJeniskelamin(rset.getString(4));
            t.setJeniskelamin(s);
        }
        return t;
    }
}
